﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using Be;
using System.Data.SqlClient;

namespace Dalc {

    /// <summary>
    /// This class is responsible for dataaccess for lessons
    /// </summary>
    public class DalcLesson {

        /// <summary>
        /// The connectionstring
        /// </summary>
        private readonly string _connect;

        /// <summary>
        /// Construct the object and set the connectionstring
        /// </summary>
        public DalcLesson() {
            //_connect = @"Data Source=DCENT\SQLEXPRESS;Initial Catalog=DIBS;Integrated Security=True";
            _connect = @"Data Source=KENNETH-PC\SQLEXPRESS;Initial Catalog=DIBS;Integrated Security=True";
            //_connect = ConfigurationManager.ConnectionStrings["TestDIBS"].ConnectionString;
        }

        /// <summary>
        /// This get's all the lessons from the database
        /// </summary>
        /// <returns> a BeLesson with all lessons in the database</returns>
        public List<BeLesson> GetLessons() {
            SqlConnection con = new SqlConnection(_connect);
            List<BeLesson> lessons = new List<BeLesson>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Lesson ORDER BY StartTime ASC";
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    string userId = sdr["UserId"].ToString();
                    int typeId = (int) sdr["TypeId"];
                    string comments = sdr["Comments"].ToString();
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    lessons.Add(new BeLesson(id, starttime, endtime, userId, comments, typeId));
                }
                return lessons;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get all lessons in a specific week
        /// </summary>
        /// <param name="startTime">The start day</param>
        /// <param name="days">The days from the start day</param>
        /// <returns>The Lessons</returns>
        public List<BeLesson> GetEvents(DateTime startTime, int days = 7) {
            SqlConnection con = new SqlConnection(_connect);
            List<BeLesson> lessons = new List<BeLesson>();
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT Id, UserId, StartTime, EndTime, Comments FROM Lesson WHERE NOT ((EndTime <= @start) OR (StartTime >= @end))";
                cmd.Parameters.AddWithValue("@start", startTime);
                cmd.Parameters.AddWithValue("@end", startTime.AddDays(days));
                con.Open();
                cmd.ExecuteNonQuery();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    string userId = sdr["UserId"].ToString();
                    string comment = sdr["Comments"].ToString();
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    lessons.Add(new BeLesson(id, starttime, endtime, comment, userId));
                }
                return lessons;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Get a lesson from an id
        /// </summary>
        /// <param name="id">The id to get the lesson from</param>
        /// <returns>The lesson</returns>
        public BeLesson GetLessonFromId(int id) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                BeLesson lesson = null;
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Lesson WHERE id = @id";
                cmd.Parameters.AddWithValue("@id", id);
                con.Open();
                cmd.ExecuteNonQuery();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int Id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    string userId = sdr["UserId"].ToString();
                    int typeid = (int) sdr["TypeId"];
                    string comments = sdr["Comments"].ToString();
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    lesson = new BeLesson(Id, starttime, endtime, userId, comments, typeid);
                }
                return lesson;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Save a lesson
        /// </summary>
        /// <param name="lesson">The lesson to save</param>
        /// <returns>True if the lesson is saved. False otherwise</returns>
        public bool SaveLesson(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "INSERT INTO Lesson (StartTime, EndTime, UserId, Comments, TypeId) VALUES (@starttimes, @endtime, @userId, @comments, @typeId)";
                cmd.Parameters.AddWithValue("@starttimes", lesson.StartTime);
                cmd.Parameters.AddWithValue("@endtime", lesson.EndTime);
                cmd.Parameters.AddWithValue("@userId", lesson.UserId);
                cmd.Parameters.AddWithValue("@comments", lesson.Comments);
                cmd.Parameters.AddWithValue("@typeId", lesson.TypeId);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Edit a lesson
        /// </summary>
        /// <param name="lesson">The lesson to edit</param>
        /// <returns>True if the lesson is edited. False otherwise</returns>
        public bool EditLesson(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "UPDATE Lesson SET StartTime = @startTime, EndTime = @endTime, Comments = @comments, TypeId = @typeId WHERE Id = @id";
                cmd.Parameters.AddWithValue("@startTime", lesson.StartTime);
                cmd.Parameters.AddWithValue("@endTime", lesson.EndTime);
                cmd.Parameters.AddWithValue("@comments", lesson.Comments);
                cmd.Parameters.AddWithValue("@typeId", lesson.TypeId);
                cmd.Parameters.AddWithValue("@id", lesson.Id);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Edit a comment from a lesson
        /// </summary>
        /// <param name="lesson">The lesson to edit the comment from</param>
        /// <returns>True if the lesson is edited. False otherwise</returns>
        public bool EditComment(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "UPDATE Lesson SET Comments = @comments WHERE Id = @id";
                cmd.Parameters.AddWithValue("@comments", lesson.Comments);
                cmd.Parameters.AddWithValue("@id", lesson.Id);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Deletes a lesson
        /// </summary>
        /// <param name="lesson">The lesson to delete</param>
        /// <returns>True if the lesson is deleted. False otherwise</returns>
        public bool DeleteLesson(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "DELETE FROM Lesson WHERE Id = @id";
                cmd.Parameters.AddWithValue("@id", lesson.Id);
                con.Open();
                int affectedRows = cmd.ExecuteNonQuery();
                return affectedRows > 0;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Check if the lesson overlaps a existing lesson
        /// </summary>
        /// <param name="lesson">The lesson to check</param>
        /// <returns>True if the lesson overlaps. False otherwise</returns>
        public bool OverlapsLessons(BeLesson lesson) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText =
                    "SELECT * FROM Lesson WHERE StartTime < @endTime AND EndTime > @startTime AND UserId = @userId AND Id != @Id";
                cmd.Parameters.AddWithValue("@endTime", lesson.EndTime);
                cmd.Parameters.AddWithValue("@startTime", lesson.StartTime);
                cmd.Parameters.AddWithValue("@userId", lesson.UserId);
                cmd.Parameters.AddWithValue("@id", lesson.Id);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                return sdr.HasRows;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }

        /// <summary>
        /// Check if there is a lesson to the booking
        /// </summary>
        /// <param name="booking">The booking to check existing lesson from</param>
        /// <returns>The lesson if it is found. Null otherwise</returns>
        public BeLesson LessonPresents(BeBooking booking) {
            SqlConnection con = new SqlConnection(_connect);
            try {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM Lesson WHERE StartTime <= @startTime AND EndTime >= @endTime AND UserId = @teacherId";
                cmd.Parameters.AddWithValue("@starttime", booking.StartTime);
                cmd.Parameters.AddWithValue("@endtime", booking.EndTime);
                cmd.Parameters.AddWithValue("@teacherId", booking.TeacherId);
                BeLesson lesson = null;
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while(sdr.Read()) {
                    int id = (int) sdr["Id"];
                    string start = sdr["StartTime"].ToString();
                    string end = sdr["EndTime"].ToString();
                    string userId = sdr["UserId"].ToString();
                    int typeId = (int) sdr["TypeId"];
                    string comments = sdr["Comments"].ToString();
                    DateTime starttime = Convert.ToDateTime(start);
                    DateTime endtime = Convert.ToDateTime(end);
                    lesson = new BeLesson(id, starttime, endtime, userId, comments, typeId);
                }
                return lesson;
            } catch(Exception ex) {
                throw new Exception(ex.Message);
            } finally {
                con.Close();
            }
        }
    }
}